package com.esm.daoimpl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.esm.dao.AttendDao;
import com.esm.utils.JDBCUtil;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

/**
 * AttendDaoImpl
 * 员工出勤Dao层实现类
 * @author 魏长均
 *
 */
public class AttendDaoImpl implements AttendDao
{
	public JSONArray findCurrentOTimeDefaultLoad(String state_zxk,int currentYear,int page,int pageSize) 
	{
		JSONArray jaoOtime = new JSONArray();
		//数据库操作SQL数据
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM ems_attend");
		sql.append(" WHERE state_zxk=?");
		sql.append(" AND YEAR(date)=?");
		sql.append(" LIMIT ?,?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, state_zxk);
			pst.setInt(2, currentYear);
			pst.setInt(3, (page-1)*pageSize);
			pst.setInt(4, pageSize);
			//数据库执行查询语句
			ResultSet rs = pst.executeQuery();
			while(rs.next())
			{
				JSONObject jo = new JSONObject();
				jo.put("att_id", rs.getInt("att_id"));
				jo.put("uname", rs.getString("uname"));
				jo.put("state_zxk", rs.getString("state_zxk"));
				jo.put("time", rs.getString("time"));
				jo.put("date", rs.getString("date"));
				jo.put("remark", rs.getString("remark"));
				jo.put("uid", rs.getInt("uid"));
				jaoOtime.add(jo);
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return jaoOtime;
	}

	public int findOtimeTotalRows(String state_zxk, int currentYear) 
	{
		int count=0;
		//数据库操作SQL数据
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(att_id) AS acount");
		sql.append(" FROM ems_attend");
		sql.append(" WHERE state_zxk=?");
		sql.append(" AND YEAR(date)=?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, state_zxk);
			pst.setInt(2, currentYear);
			//数据库执行查询语句
			ResultSet rs = pst.executeQuery();
			if(rs.next())
			{
				count = rs.getInt("acount");
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return count;
	}

	public JSONArray findSelectYearOTimeLoad(String state_zxk, int selectYear, int page, int pageSize) 
	{
		JSONArray jaoSelectYrOtime = new JSONArray();
		//数据库操作SQL数据
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM ems_attend");
		sql.append(" WHERE state_zxk=?");
		sql.append(" AND YEAR(date)=?");
		sql.append(" LIMIT ?,?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, state_zxk);
			pst.setInt(2, selectYear);
			pst.setInt(3, (page-1)*pageSize);
			pst.setInt(4, pageSize);
			//数据库执行查询语句
			ResultSet rs = pst.executeQuery();
			while(rs.next())
			{
				JSONObject jo = new JSONObject();
				jo.put("att_id", rs.getInt("att_id"));
				jo.put("uname", rs.getString("uname"));
				jo.put("state_zxk", rs.getString("state_zxk"));
				jo.put("time", rs.getString("time"));
				jo.put("date", rs.getString("date"));
				jo.put("remark", rs.getString("remark"));
				jo.put("uid", rs.getInt("uid"));
				jaoSelectYrOtime.add(jo);
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return jaoSelectYrOtime;
	}

	public JSONArray findYrMthOTimeLoad(String state_zxk, int selectYear, int selectMonth, int page, int pageSize) 
	{
		JSONArray jaoSelectYrMthOtime = new JSONArray();
		//数据库操作SQL数据
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM ems_attend");
		sql.append(" WHERE state_zxk=?");
		sql.append(" AND YEAR(date)=?");
		sql.append(" AND Month(date)=?");
		sql.append(" LIMIT ?,?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, state_zxk);
			pst.setInt(2, selectYear);
			pst.setInt(3, selectMonth);
			pst.setInt(4, (page-1)*pageSize);
			pst.setInt(5, pageSize);
			//数据库执行查询语句
			ResultSet rs = pst.executeQuery();
			while(rs.next())
			{
				JSONObject jo = new JSONObject();
				jo.put("att_id", rs.getInt("att_id"));
				jo.put("uname", rs.getString("uname"));
				jo.put("state_zxk", rs.getString("state_zxk"));
				jo.put("time", rs.getString("time"));
				jo.put("date", rs.getString("date"));
				jo.put("remark", rs.getString("remark"));
				jo.put("uid", rs.getInt("uid"));
				jaoSelectYrMthOtime.add(jo);
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return jaoSelectYrMthOtime;
	}

	public JSONArray findYrMthDayOTimeLoad(String state_zxk, int selectYear, int selectMonth, int selectDay, int page,
			int pageSize) 
	{
		JSONArray jaoSelectYrMthDayOtime = new JSONArray();
		//数据库操作SQL数据
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM ems_attend");
		sql.append(" WHERE state_zxk=?");
		sql.append(" AND YEAR(date)=?");
		sql.append(" AND Month(date)=?");
		sql.append(" AND DAY(date)=?");
		sql.append(" LIMIT ?,?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, state_zxk);
			pst.setInt(2, selectYear);
			pst.setInt(3, selectMonth);
			pst.setInt(4, selectDay);
			pst.setInt(5, (page-1)*pageSize);
			pst.setInt(6, pageSize);
			//数据库执行查询语句
			ResultSet rs = pst.executeQuery();
			while(rs.next())
			{
				JSONObject jo = new JSONObject();
				jo.put("att_id", rs.getInt("att_id"));
				jo.put("uname", rs.getString("uname"));
				jo.put("state_zxk", rs.getString("state_zxk"));
				jo.put("time", rs.getString("time"));
				jo.put("date", rs.getString("date"));
				jo.put("remark", rs.getString("remark"));
				jo.put("uid", rs.getInt("uid"));
				jaoSelectYrMthDayOtime.add(jo);
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return jaoSelectYrMthDayOtime;
	}

	public JSONArray findSelectDeptYearOTimeLoad(String state_zxk, int selectYear, int dep_id, int page, int pageSize) 
	{
		JSONArray jaoSelectDeptYrOtime = new JSONArray();
		//数据库操作SQL数据

		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM ems_attend");
		sql.append(" INNER JOIN ems_user");
		sql.append(" ON ems_user.uid=ems_attend.uid");
		sql.append(" WHERE state_zxk=?");
		sql.append(" AND YEAR(date)=?");
		sql.append(" AND ems_user.dep_id=?");
		sql.append(" LIMIT ?,?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, state_zxk);
			pst.setInt(2, selectYear);
			pst.setInt(3, dep_id);
			pst.setInt(4, (page-1)*pageSize);
			pst.setInt(5, pageSize);
			//数据库执行查询语句
			ResultSet rs = pst.executeQuery();
			while(rs.next())
			{
				JSONObject jo = new JSONObject();
				jo.put("att_id", rs.getInt("att_id"));
				jo.put("uname", rs.getString("uname"));
				jo.put("state_zxk", rs.getString("state_zxk"));
				jo.put("time", rs.getString("time"));
				jo.put("date", rs.getString("date"));
				jo.put("remark", rs.getString("remark"));
				jo.put("uid", rs.getInt("uid"));
				jaoSelectDeptYrOtime.add(jo);
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return jaoSelectDeptYrOtime;
	}

	public JSONArray findDeptYrMthOTimeLoad(String state_zxk, int selectYear, int selectMonth, int dep_id, int page,
			int pageSize) 
	{
		JSONArray jaoSelectDeptYrMthOtime = new JSONArray();
		//数据库操作SQL数据

		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM ems_attend");
		sql.append(" INNER JOIN ems_user");
		sql.append(" ON ems_user.uid=ems_attend.uid");
		sql.append(" WHERE state_zxk=?");
		sql.append(" AND YEAR(date)=?");
		sql.append(" AND MONTH(date)=?");
		sql.append(" AND ems_user.dep_id=?");
		sql.append(" LIMIT ?,?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, state_zxk);
			pst.setInt(2, selectYear);
			pst.setInt(3, selectMonth);
			pst.setInt(4, dep_id);
			pst.setInt(5, (page-1)*pageSize);
			pst.setInt(6, pageSize);
			//数据库执行查询语句
			ResultSet rs = pst.executeQuery();
			while(rs.next())
			{
				JSONObject jo = new JSONObject();
				jo.put("att_id", rs.getInt("att_id"));
				jo.put("uname", rs.getString("uname"));
				jo.put("state_zxk", rs.getString("state_zxk"));
				jo.put("time", rs.getString("time"));
				jo.put("date", rs.getString("date"));
				jo.put("remark", rs.getString("remark"));
				jo.put("uid", rs.getInt("uid"));
				jaoSelectDeptYrMthOtime.add(jo);
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return jaoSelectDeptYrMthOtime;
	}

	public JSONArray findDeptYrMthDayOTimeLoad(String state_zxk, int selectYear, int selectMonth, int selectDay, int dep_id,
			int page, int pageSize) 
	{
		JSONArray jaoSelectDeptYrMthDayOtime = new JSONArray();
		//数据库操作SQL数据

		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM ems_attend");
		sql.append(" INNER JOIN ems_user");
		sql.append(" ON ems_user.uid=ems_attend.uid");
		sql.append(" WHERE state_zxk=?");
		sql.append(" AND YEAR(date)=?");
		sql.append(" AND MONTH(date)=?");
		sql.append(" AND DAY(date)=?");
		sql.append(" AND ems_user.dep_id=?");
		sql.append(" LIMIT ?,?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, state_zxk);
			pst.setInt(2, selectYear);
			pst.setInt(3, selectMonth);
			pst.setInt(4, selectDay);
			pst.setInt(5, dep_id);
			pst.setInt(6, (page-1)*pageSize);
			pst.setInt(7, pageSize);
			//数据库执行查询语句
			ResultSet rs = pst.executeQuery();
			while(rs.next())
			{
				JSONObject jo = new JSONObject();
				jo.put("att_id", rs.getInt("att_id"));
				jo.put("uname", rs.getString("uname"));
				jo.put("state_zxk", rs.getString("state_zxk"));
				jo.put("time", rs.getString("time"));
				jo.put("date", rs.getString("date"));
				jo.put("remark", rs.getString("remark"));
				jo.put("uid", rs.getInt("uid"));
				jaoSelectDeptYrMthDayOtime.add(jo);
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return jaoSelectDeptYrMthDayOtime;
	}
	public int findSelectYearOtimeTotalRows(String state_zxk, int selectYear) 
	{
		int count=0;
		//数据库操作SQL数据
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(att_id) AS acount");
		sql.append(" FROM ems_attend");
		sql.append(" WHERE state_zxk=?");
		sql.append(" AND YEAR(date)=?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, state_zxk);
			pst.setInt(2, selectYear);
			//数据库执行查询语句
			ResultSet rs = pst.executeQuery();
			if(rs.next())
			{
				count = rs.getInt("acount");
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return count;
	}

	public int findYrMthOtimeTotalRows(String state_zxk, int selectYear, int selectMonth) 
	{
		int count=0;
		//数据库操作SQL数据
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(att_id) AS acount");
		sql.append(" FROM ems_attend");
		sql.append(" WHERE state_zxk=?");
		sql.append(" AND YEAR(date)=?");
		sql.append(" AND MONTH(date)=?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, state_zxk);
			pst.setInt(2, selectYear);
			pst.setInt(3, selectMonth);
			//数据库执行查询语句
			ResultSet rs = pst.executeQuery();
			if(rs.next())
			{
				count = rs.getInt("acount");
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return count;
	}

	public int findYrMthDayOtimeTotalRows(String state_zxk, int selectYear, int selectMonth, int selectDay) 
	{
		int count=0;
		//数据库操作SQL数据
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(att_id) AS acount");
		sql.append(" FROM ems_attend");
		sql.append(" WHERE state_zxk=?");
		sql.append(" AND YEAR(date)=?");
		sql.append(" AND MONTH(date)=?");
		sql.append(" AND DAY(date)=?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, state_zxk);
			pst.setInt(2, selectYear);
			pst.setInt(3, selectMonth);
			pst.setInt(4, selectDay);
			//数据库执行查询语句
			ResultSet rs = pst.executeQuery();
			if(rs.next())
			{
				count = rs.getInt("acount");
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return count;
	}
	
	public int findSelectDeptYearOtimeTotalRows(String state_zxk, int selectYear, int dep_id) 
	{
		int count=0;
		//数据库操作SQL数据
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(att_id) AS acount");
		sql.append(" FROM ems_attend");
		sql.append(" INNER JOIN ems_user");
		sql.append(" ON ems_user.uid=ems_attend.uid");
		sql.append(" WHERE state_zxk=?");
		sql.append(" AND YEAR(date)=?");
		sql.append(" AND ems_user.dep_id=?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, state_zxk);
			pst.setInt(2, selectYear);
			pst.setInt(3, dep_id);
			//数据库执行查询语句
			ResultSet rs = pst.executeQuery();
			if(rs.next())
			{
				count = rs.getInt("acount");
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return count;
	}

	public int findDeptYrMthOtimeTotalRows(String state_zxk, int selectYear, int selectMonth, int dep_id) 
	{
		int count=0;
		//数据库操作SQL数据
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(att_id) AS acount");
		sql.append(" FROM ems_attend");
		sql.append(" INNER JOIN ems_user");
		sql.append(" ON ems_user.uid=ems_attend.uid");
		sql.append(" WHERE state_zxk=?");
		sql.append(" AND YEAR(date)=?");
		sql.append(" AND MONTH(date)=?");
		sql.append(" AND ems_user.dep_id=?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, state_zxk);
			pst.setInt(2, selectYear);
			pst.setInt(3, selectMonth);
			pst.setInt(4, dep_id);
			//数据库执行查询语句
			ResultSet rs = pst.executeQuery();
			if(rs.next())
			{
				count = rs.getInt("acount");
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return count;
	}

	public int findDeptYrMthDayOtimeTotalRows(String state_zxk, int selectYear, int selectMonth, int selectDay,
			int dep_id) 
	{
		int count=0;
		//数据库操作SQL数据
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(att_id) AS acount");
		sql.append(" FROM ems_attend");
		sql.append(" INNER JOIN ems_user");
		sql.append(" ON ems_user.uid=ems_attend.uid");
		sql.append(" WHERE state_zxk=?");
		sql.append(" AND YEAR(date)=?");
		sql.append(" AND MONTH(date)=?");
		sql.append(" AND DAY(date)=?");
		sql.append(" AND ems_user.dep_id=?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, state_zxk);
			pst.setInt(2, selectYear);
			pst.setInt(3, selectMonth);
			pst.setInt(4, selectDay);
			pst.setInt(5, dep_id);
			//数据库执行查询语句
			ResultSet rs = pst.executeQuery();
			if(rs.next())
			{
				count = rs.getInt("acount");
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return count;
	}

	public JSONArray findSelectDempYearOTimeLoad(String state_zxk, int selectYear, int uid, int page, int pageSize) 
	{
		JSONArray jaoSelectDempYrOtime = new JSONArray();
		//数据库操作SQL数据
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM ems_attend");
		sql.append(" WHERE state_zxk=?");
		sql.append(" AND YEAR(date)=?");
		sql.append(" AND uid=?");
		sql.append(" LIMIT ?,?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, state_zxk);
			pst.setInt(2, selectYear);
			pst.setInt(3, uid);
			pst.setInt(4, (page-1)*pageSize);
			pst.setInt(5, pageSize);
			//数据库执行查询语句
			ResultSet rs = pst.executeQuery();
			while(rs.next())
			{
				JSONObject jo = new JSONObject();
				jo.put("att_id", rs.getInt("att_id"));
				jo.put("uname", rs.getString("uname"));
				jo.put("state_zxk", rs.getString("state_zxk"));
				jo.put("time", rs.getString("time"));
				jo.put("date", rs.getString("date"));
				jo.put("remark", rs.getString("remark"));
				jo.put("uid", rs.getInt("uid"));
				jaoSelectDempYrOtime.add(jo);
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return jaoSelectDempYrOtime;
	}
	
	public JSONArray findDempYrMthOTimeLoad(String state_zxk, int selectYear, int selectMonth, int uid, int page,
			int pageSize) 
	{
		JSONArray jaoSelectDempYrMthOtime = new JSONArray();
		//数据库操作SQL数据
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM ems_attend");
		sql.append(" WHERE state_zxk=?");
		sql.append(" AND YEAR(date)=?");
		sql.append(" AND MONTH(date)=?");
		sql.append(" AND uid=?");
		sql.append(" LIMIT ?,?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, state_zxk);
			pst.setInt(2, selectYear);
			pst.setInt(3, selectMonth);
			pst.setInt(4, uid);
			pst.setInt(5, (page-1)*pageSize);
			pst.setInt(6, pageSize);
			//数据库执行查询语句
			ResultSet rs = pst.executeQuery();
			while(rs.next())
			{
				JSONObject jo = new JSONObject();
				jo.put("att_id", rs.getInt("att_id"));
				jo.put("uname", rs.getString("uname"));
				jo.put("state_zxk", rs.getString("state_zxk"));
				jo.put("time", rs.getString("time"));
				jo.put("date", rs.getString("date"));
				jo.put("remark", rs.getString("remark"));
				jo.put("uid", rs.getInt("uid"));
				jaoSelectDempYrMthOtime.add(jo);
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return jaoSelectDempYrMthOtime;
	}
	
	public JSONArray findDempYrMthDayOTimeLoad(String state_zxk, int selectYear, int selectMonth, int selectDay, int uid,
			int page, int pageSize) 
	{
		JSONArray jaoSelectDempYrMthDayOtime = new JSONArray();
		//数据库操作SQL数据

		StringBuffer sql = new StringBuffer();
		sql.append("SELECT * FROM ems_attend");
		sql.append(" WHERE state_zxk=?");
		sql.append(" AND YEAR(date)=?");
		sql.append(" AND MONTH(date)=?");
		sql.append(" AND DAY(date)=?");
		sql.append(" AND uid=?");
		sql.append(" LIMIT ?,?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, state_zxk);
			pst.setInt(2, selectYear);
			pst.setInt(3, selectMonth);
			pst.setInt(4, selectDay);
			pst.setInt(5, uid);
			pst.setInt(6, (page-1)*pageSize);
			pst.setInt(7, pageSize);
			//数据库执行查询语句
			ResultSet rs = pst.executeQuery();
			while(rs.next())
			{
				JSONObject jo = new JSONObject();
				jo.put("att_id", rs.getInt("att_id"));
				jo.put("uname", rs.getString("uname"));
				jo.put("state_zxk", rs.getString("state_zxk"));
				jo.put("time", rs.getString("time"));
				jo.put("date", rs.getString("date"));
				jo.put("remark", rs.getString("remark"));
				jo.put("uid", rs.getInt("uid"));
				jaoSelectDempYrMthDayOtime.add(jo);
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return jaoSelectDempYrMthDayOtime;
	}

	public int findSelectDempYearOtimeTotalRows(String state_zxk, int selectYear, int uid) 
	{
		int count=0;
		//数据库操作SQL数据
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(att_id) AS acount");
		sql.append(" FROM ems_attend");
		sql.append(" WHERE state_zxk=?");
		sql.append(" AND YEAR(date)=?");
		sql.append(" AND uid=?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, state_zxk);
			pst.setInt(2, selectYear);
			pst.setInt(3, uid);
			//数据库执行查询语句
			ResultSet rs = pst.executeQuery();
			if(rs.next())
			{
				count = rs.getInt("acount");
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return count;
	}

	public int findDempYrMthOtimeTotalRows(String state_zxk, int selectYear, int selectMonth, int uid) 
	{
		int count=0;
		//数据库操作SQL数据
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(att_id) AS acount");
		sql.append(" FROM ems_attend");
		sql.append(" WHERE state_zxk=?");
		sql.append(" AND YEAR(date)=?");
		sql.append(" AND MONTH(date)=?");
		sql.append(" AND uid=?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, state_zxk);
			pst.setInt(2, selectYear);
			pst.setInt(3, selectMonth);
			pst.setInt(4, uid);
			//数据库执行查询语句
			ResultSet rs = pst.executeQuery();
			if(rs.next())
			{
				count = rs.getInt("acount");
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return count;
	}

	public int findDempYrMthDayOtimeTotalRows(String state_zxk, int selectYear, int selectMonth, int selectDay,
			int uid) 
	{
		int count=0;
		//数据库操作SQL数据
		StringBuffer sql = new StringBuffer();
		sql.append("SELECT COUNT(att_id) AS acount");
		sql.append(" FROM ems_attend");
		sql.append(" WHERE state_zxk=?");
		sql.append(" AND YEAR(date)=?");
		sql.append(" AND MONTH(date)=?");
		sql.append(" AND DAY(date)=?");
		sql.append(" AND uid=?");
		//定义数据库连接对象
		Connection con = null;
		//定义预编译对象
		PreparedStatement pst = null;
		try 
		{
			//连接数据库
			con = JDBCUtil.getConnection();
			//生成预编译对象
			pst = con.prepareStatement(sql.toString());
			pst.setString(1, state_zxk);
			pst.setInt(2, selectYear);
			pst.setInt(3, selectMonth);
			pst.setInt(4, selectDay);
			pst.setInt(5, uid);
			//数据库执行查询语句
			ResultSet rs = pst.executeQuery();
			if(rs.next())
			{
				count = rs.getInt("acount");
			}
		} 
		catch (SQLException e) 
		{
			e.printStackTrace();
		}
		JDBCUtil.closeJDBC(pst, con);
		return count;
	}
}
